package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Timestamp;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.I_TRM_Application;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MTRMApplication;
import org.compiere.model.MYear;
import org.compiere.model.Query;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

/**
 * @author Aslanbek
 *
 */
public class TRM_ApplicationsReport extends SvrProcess {
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	/**Current Record_ID */
	int p_TRM_Application_ID = 0;
	/** Period Parameter */
	private Timestamp p_DateAcct_From = null;
	private Timestamp p_DateAcct_To = null;
	private String p_DocStatus = null;
	private int p_C_Year_ID = 0;
	/**Out file */
	String ResultPath;
	protected void prepare() {
		log.info("TRM_ApplicationsReport Report Prepare method begin");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();	
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("TRM_Application_ID")) 
				p_TRM_Application_ID  = para[i].getParameterAsInt();
			else if (name.equals("DateFrom")) 
				p_DateAcct_From = (Timestamp) para[i].getParameter();
			else if(name.equals("DateTo"))
				p_DateAcct_To = (Timestamp) para[i].getParameter();
			else if(name.equals("DocStatus"))
				p_DocStatus = (String)para[i].getParameter();
			else if(name.equals("C_Year_ID"))
				p_C_Year_ID = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	protected String doIt() throws Exception {
		//File path
		String fileName = "Финансовая заявка";
		if(p_TRM_Application_ID == 0)
			fileName = "Финансовые заявки";
		//возможность расширение функционала отчета
		if(p_DocStatus==null)
			p_DocStatus = "PA";
		MYear year = null;
		int fiscalYear = 0;
		if(p_C_Year_ID>0)
			year = new MYear(m_ctx, p_C_Year_ID, get_TrxName());
		if(year!=null)
		{
			fiscalYear = Integer.valueOf(year.getFiscalYear());
			p_DateAcct_From = Timestamp.valueOf(fiscalYear+"-01-01 00:00:00.0");
			p_DateAcct_To = Timestamp.valueOf(fiscalYear+"-12-31 00:00:00.0");
			fileName = fileName +" на " +fiscalYear;
		}
		//
		ResultPath = Util.getResultPath(fileName, fileName.length()+1);
		// 
		  MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		XSSFWorkbook wb = new XSSFWorkbook(entry.getInputStream());
		XSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		//Cells style
		R = sheet.getRow(0);
		CellStyle csInt = R.getCell(10).getCellStyle();
		CellStyle csDate = R.getCell(12).getCellStyle();
		CellStyle csGenText = R.getCell(14).getCellStyle();
		CellStyle csBoldItalicText = R.getCell(16).getCellStyle();
		CellStyle csNumber = R.getCell(18).getCellStyle();
		CellStyle csEmpty = R.getCell(20).getCellStyle();
		
		//Report Body
		int rowNo = 1;
		for(MTRMApplication application : getTRMApplications(p_TRM_Application_ID, p_DateAcct_From, p_DateAcct_To, p_DocStatus))
		{
			R = sheet.createRow(rowNo);
			//col 1 №

			C = R.createCell(0);
			C.setCellStyle(csInt);
			C.setCellValue(rowNo);
			
			//col 2 дата
			C = R.createCell(1);
			C.setCellStyle(csDate);
			C.setCellValue(application.getDateAcct());
			
			//col 3 Номер заявки
			C = R.createCell(2);
			C.setCellStyle(csGenText);
			C.setCellValue(application.getDocumentNo());
			//col 4 Инициаторы заявок
			C = R.createCell(3);
			C.setCellStyle(csGenText);
			C.setCellValue(application.getInitiator().getDescription());
			//col 5 Поставщик
			C = R.createCell(4);
			C.setCellStyle(csBoldItalicText);
			C.setCellValue(application.getCMS_Contract().getC_BPartner().getName());
			//col 6 подтверждающий документ
			C = R.createCell(5);
			C.setCellStyle(csGenText);
			C.setCellValue(application.getApps());
			//col 7 наименование товара
			C = R.createCell(6);
			C.setCellStyle(csGenText);
			C.setCellValue(application.getDescription());
			//col 8 оплата %
			C = R.createCell(7);
			C.setCellStyle(csGenText);
			C.setCellValue(application.getCMS_Contract().getPaymentTerms());
			//col 9 оплата тенге
			C = R.createCell(8);
			C.setCellStyle(csNumber);
			C.setCellValue(application.getApplAmount() == null? 0: application.getApplAmount().doubleValue());
			
			rowNo++;
		}
		
		if(rowNo==1){
			R = sheet.createRow(rowNo);
			for(int i=0; i< 5; i++)
				R.createCell(i).setCellStyle(csBoldItalicText);;
				
			C = R.getCell(0);
			C.setCellValue("По заданным параметрам данные отсутсвуют");
			C.setCellStyle(csBoldItalicText);
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 4));
		}
			
		//Delete temp Cells style
		R = sheet.getRow(0);
		
		R.getCell(10).setCellStyle(csEmpty);
		R.getCell(10).setCellValue("");
		
		R.getCell(12).setCellStyle(csEmpty);
		R.getCell(12).setCellValue("");
		
		R.getCell(14).setCellStyle(csEmpty);
		R.getCell(14).setCellValue("");
		
		R.getCell(16).setCellStyle(csEmpty);
		R.getCell(16).setCellValue("");
		
		R.getCell(18).setCellStyle(csEmpty);
		R.getCell(18).setCellValue("");
		
		R.getCell(20).setCellValue("");
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));	
		return Msg.translate(m_ctx, "Success");
	}

	private MTRMApplication[] getTRMApplications(int TRM_Application_ID, Timestamp DateFrom, Timestamp DateTo, String DocStatus)
	{
		String whereClause = "";
		
		if(DateFrom != null && DateTo !=null)
		{
				whereClause = "DateAcct BETWEEN '"+DateFrom+"' AND '"+DateTo+"' ";
		}
		else if(DateFrom != null || DateTo !=null)
		{
			if(DateFrom !=null)
				whereClause = "DateAcct > '"+DateFrom+"' ";
			else
				whereClause = "DateAcct < '"+DateTo+"' ";
		}
		if(TRM_Application_ID>0)
			if(whereClause.length()>0)
				whereClause = whereClause+" AND TRM_Application_ID = "+TRM_Application_ID;
			else
				whereClause = " TRM_Application_ID = "+TRM_Application_ID;
		if(whereClause.length()>0)
			whereClause = whereClause + " AND DocStatus = '"+DocStatus+"'";
		else
			whereClause = " DocStatus = '"+DocStatus+"'";
		
		List<MTRMApplication> list = new Query(getCtx(), I_TRM_Application.Table_Name, whereClause, get_TrxName())
										.setOrderBy(I_TRM_Application.COLUMNNAME_DateAcct)
										.list();
		
		return list.toArray(new MTRMApplication[list.size()]);
	}
}
